/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DBLager;
import java.sql.*;
import java.util.ArrayList;
import ModelLager.CustomerP;

/**
 *
 * @author Alex Ly
 */
public class DbCustomerP implements IFDbCustomerP {
    private Connection con;
    private PreparedStatement pStmtSelect;
    private ArrayList<CustomerP> customerP;
    
    public DbCustomerP()
    {
        con = DbConnection2.getInstance().getDBcon();
    }
    
    public CustomerP findCustomerP(int customerID, boolean retriveAssociation)
    {
        CustomerP cObj = new CustomerP();
       // proObj = singleWhere("pnumber = " + pnumber, false);
        cObj = findWithPreQuery(customerID);
        return cObj;
    }
    
    /*
    public ArrayList<CustomerP> findWithPreQuery(int customerID)
        {   System.out.println("Find with preCompiled");
        
            ArrayList<CustomerP> list = new ArrayList<CustomerP>();
        
            if(pStmtSelect == null){
               pStmtSelect =  getPreparedStmt();          
            }
            CustomerP cPObj = null;
            ResultSet results;
            try{
           
            pStmtSelect.setInt(1, customerID);
            results = pStmtSelect.executeQuery();
            while(results.next()){
                 cPObj = buildCustomerP(results);
                 list.add(cPObj);
            }
            
            }
            catch(Exception e){
                e.getMessage();
            }
            return list;
        }
    */
    

    public int insertCustomerP(CustomerP cObj, boolean retriveAssociation)
    {
        int rc = -1;
        PreparedStatement pstmt = null;
        String insert = "insert into CustomerP (name, address, zipCode, phoneNo, country, customerID, discount, bonusSystem)" +
                "values (?,?,?,?,?,?,?,?)";
        System.out.println(insert);
        try
        {
           
            pstmt = con.prepareStatement(insert);
            pstmt.setString(1,cObj.getName());
            pstmt.setString(2,cObj.getAddress());
            pstmt.setString(3,cObj.getZipCode());
            pstmt.setString(4,cObj.getPhoneNo());
            pstmt.setString(5,cObj.getCountry());
            pstmt.setInt(6,cObj.getCustomerID());
            pstmt.setDouble(7,cObj.getDiscount());
            pstmt.setDouble(8,cObj.getBonusSystem());

            
            rc = pstmt.executeUpdate();
        }
        catch(SQLException sqlE)
        {    
             System.out.println("SQL Error");  
             System.out.println(sqlE.getMessage());
        }
        catch(Exception e)
        {
            e.getMessage();
        }
        return rc;
    }
    
    public int deleteCustomerP(int customerID)
    {
        int rc = -1;
        PreparedStatement pstmt = null;
        String delete = "delete from customerP where customerID = ?";
        System.out.print(delete);
        try
        {
            pstmt = con.prepareStatement(delete);
            pstmt.setInt(1, customerID);
            rc = pstmt.executeUpdate();
        }
        catch(SQLException sqlE)
             {
                 System.out.println("SQL Error");
                 System.out.println(sqlE.getMessage());   
             }
             catch(Exception e){
                 e.getMessage();
                 
             }
            return rc;
        }
    
    public ArrayList<CustomerP> findByIDWithPreQuery(int customerID, boolean allID)
    {
        System.out.println("Find with preCompiled");
        ArrayList<CustomerP> list = new ArrayList<CustomerP>();
        String parameter ="";
        String wClause = "";
        if(pStmtSelect == null)
        {
        if(!allID)
            {
                wClause = "ID like ?";
            }
            else
            {
                wClause ="";
            }

        }
        CustomerP cObj = null;
        ResultSet results;
        try
        {
            if(!allID)
            {
                parameter = "%"+customerID+"%"; // betyder at det der står foran navn og efter navn kommer med i resultaterne. f.eks. Søg på john og du får johnson + andre med john i deres navn.
                pStmtSelect.clearParameters(); //FORSØG
                pStmtSelect.setString(1, parameter);
            }
            
            results = pStmtSelect.executeQuery();
            while(results.next())
            {
                cObj = buildCustomerP(results);
                list.add(cObj);
            }
            pStmtSelect.close();
        }
        catch(Exception e)
        {
            System.out.println("ERROR: " + e.getMessage());
        }
        return list;    
    }
    
    public ArrayList<CustomerP> getAllCustomer()
    {      
            PreparedStatement preStmnt = null;          
            ArrayList<CustomerP> list = new ArrayList<CustomerP>();
            ResultSet results;
            CustomerP cObj = null;
            try{    
            String query = "SELECT * FROM CustomerP";     
            preStmnt = con.prepareStatement(query);
            results = preStmnt.executeQuery();
            
            while(results.next()){
                 cObj = buildCustomerP(results);
                 list.add(cObj); 
            }
            preStmnt.close();
            }catch(SQLException SQLe)
            {
                System.out.println("SQL Exception: " + SQLe);
            }
            catch(Exception e){
                System.out.println("Fejl i findAllCustomer(): " + e.getMessage());
                
            }
            
            return list;
        
    }
    
    public int updateCustomerP(CustomerP customer)
    {
        // metoden returnerer rc = -1(rc er kort for return code), hvis ikke 'try løkken' 
        // går igennem og vil derfor returnere en fejl.
        int rc = -1;
        //opretter et tomt statement objekt
        //opretter en string der indeholder querien der skal eksekveres
        String query = null;
        
        //fordi con.prepareStatement metoden "kaster" en fejl fejl(SQLException)
        //skal koden indrammes i try/catch
        try
        {
        
            query = "UPDATE CustomerP " 
                  + "SET name = ?, address = ?, zipCode = ?, phoneNo = ?, country = ?, customerID = ?, discount = ?, bonusSystem =?"
                  + " WHERE customerID = " + customer.getCustomerID();

            PreparedStatement preStmnt = con.prepareStatement(query);
            preStmnt.setQueryTimeout(5);
            System.out.println(query);

            preStmnt.setString(1, customer.getName());
            preStmnt.setString(2, customer.getAddress());
            preStmnt.setString(3, customer.getZipCode());
            preStmnt.setString(4, customer.getPhoneNo());
            preStmnt.setString(5, customer.getCountry());
            preStmnt.setInt(6, customer.getCustomerID());
            preStmnt.setDouble(7, customer.getDiscount());
            preStmnt.setDouble(8, customer.getBonusSystem());
            rc = preStmnt.executeUpdate();
            
            preStmnt.close();
                
        }
        catch(SQLException SQLe)
        {
            //returnerer SQL Exception
            System.out.println("SQL Error: " + SQLe.getMessage());
        }
        catch(Exception e)
        {
            //returnerer java exception
            System.out.println("ExceptionE: " + e.getMessage());
        }        
        return rc;
    }


    
    private CustomerP buildCustomerP(ResultSet results)
    {
       CustomerP cObj = new CustomerP();
       
         try{
               cObj.setName(results.getString("name"));
               cObj.setAddress(results.getString("address"));
               cObj.setZipCode(results.getString("zipCode"));
               cObj.setPhoneNo(results.getString("phoneNo"));
               cObj.setCountry(results.getString("country"));
               cObj.setCustomerID(results.getInt("customerID"));
               cObj.setDiscount(results.getDouble("discount"));
               cObj.setBonusSystem(results.getDouble("bonusSystem"));
      
               
          }
         catch(Exception e)
         {
             System.out.println("building customerP object");
         }
        
        return cObj;
        
    }
    
    private String buildQuery(String wClause)
	{
            String query="SELECT * FROM CustomerP"; 
            
           

		if (wClause.length()>0)
			query = query + " where " + wClause;
			
		return query;
	}
    
    public CustomerP findWithPreQuery( int customerID)
        {   System.out.println("Find with preCompiled");
            if(pStmtSelect == null){
               pStmtSelect =  getPreparedStmt();          
            }
            CustomerP cObj = null;
            ResultSet results;
            try{
           
            pStmtSelect.setInt(1,customerID);
            results = pStmtSelect.executeQuery();
            if(results.next()){
                 cObj = buildCustomerP(results);	
            }
            }
            catch(Exception e){
                e.getMessage();
            }
            return cObj;
       }
    
    private PreparedStatement getPreparedStmt()
        {
           try{ 
            String query = "select * from CustomerP" +
                           " where customerID = ?";
            System.out.println("Preparestatement " + query);
            pStmtSelect = con.prepareStatement(query);
            
           }
           catch(Exception e){
                e.getMessage();
            }
           return pStmtSelect;
        }
    
}
